﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Linq.Dynamic;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace System.Linq.Dynamic
{
	[System.Diagnostics.CodeAnalysis.SuppressMessage( "BTR.Globalization", "MH1000:StringLiteralsShouldBeInResxOrIgnored", Justification = "Code, not text.", Target="*:MH1000:StringLiteralsShouldBeInResxOrIgnored" )]
	public static class LinqToSqlExtensions
	{
		/// <summary>
		/// Creates a *.csv file from an IQueryable query, dumping out the 'simple' properties/fields.
		/// </summary>
		/// <param name="query">Represents a SELECT query to execute.</param>
		/// <param name="fileName">The name of the file to create.</param>
		/// <remarks>
		/// <para>If the file specified by <paramref name="fileName"/> exists, it will be deleted.</para>
		/// <para>If the <paramref name="query"/> contains any properties that are entity sets (i.e. rows from a FK relationship) the values will not be dumped to the file.</para>
		/// <para>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</para>
		/// </remarks>
        public static T DumpCSV<T>( this T query, string fileName ) where T : System.Collections.IEnumerable
		{
			return query.DumpCSV( fileName, true, null );
		}

		/// <summary>
		/// Creates a *.csv file from an IQueryable query, dumping out the 'simple' properties/fields.
		/// </summary>
		/// <param name="query">Represents a SELECT query to execute.</param>
		/// <param name="fileName">The name of the file to create.</param>
		/// <param name="deleteFile">Whether or not to delete the file specified by <paramref name="fileName"/> if it exists.</param>
		/// <remarks>
		/// <para>If the <paramref name="query"/> contains any properties that are entity sets (i.e. rows from a FK relationship) the values will not be dumped to the file.</para>
		/// <para>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</para>
		/// </remarks>
        public static T DumpCSV<T>( this T query, string fileName, bool deleteFile, Func<MemberInfo, object, object> translateValue = null ) where T : System.Collections.IEnumerable
		{
			if ( File.Exists( fileName ) && deleteFile )
			{
				File.Delete( fileName );
			}

			using ( var output = new FileStream( fileName, FileMode.CreateNew ) )
			{
				using ( var writer = new StreamWriter( output ) )
				{
					var firstRow = true;

					PropertyInfo[] properties = null;
					FieldInfo[] fields = null;
					Type type = null;
					bool typeIsAnonymous = false;

					foreach ( var r in query )
					{
						if ( type == null )
						{
							type = r.GetType();
							typeIsAnonymous = type.IsAnonymous();
                            properties = type.GetProperties().Where( p => IsCsvExportAllowed( p.PropertyType, (MemberInfo)p ) ).ToArray();
                            fields = type.GetFields().Where( p => IsCsvExportAllowed( p.FieldType, (MemberInfo)p ) ).ToArray();
						}

						var firstCol = true;

						if ( firstRow )
						{

                            foreach ( var p in properties )
                            {
                                if ( !firstCol ) writer.Write( "," );
                                else { firstCol = false; }

                                writer.Write( CsvHeaderName( (MemberInfo)p ) );
                            }

                            foreach ( var p in fields )
                            {
                                if ( !firstCol ) writer.Write( "," );
                                else { firstCol = false; }

                                writer.Write( CsvHeaderName( (MemberInfo)p ) );
                            }
							
							writer.WriteLine();
						}
						firstRow = false;
						firstCol = true;

                        foreach ( var p in properties )
						{
							if ( !firstCol ) writer.Write( "," );
							else { firstCol = false; }

                            var value = translateValue != null
                                ? translateValue( p, r )
                                : p.GetValue( r, null );

							DumpValue( value, writer );
						}

                        foreach ( var p in fields )
                        {
                            if ( !firstCol ) writer.Write( "," );
                            else { firstCol = false; }

                            var value = translateValue != null
                                ? translateValue( p, r )
                                : p.GetValue( r );

                            DumpValue( value, writer );
                        }

						writer.WriteLine();
					}
				}
			}

            return query;
		}

        /// <summary>
        /// Returns all fields/properties from <paramref name="source"/> except for the field(s)/property(ies) listed in the selector expression.
        /// </summary>
        public static IQueryable SelectExcept<TSource, TResult>( this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector )
        {
            var newExpression = selector.Body as NewExpression;

            var excludeProperties = newExpression != null
                    ? newExpression.Members.Select( m => m.Name )
                    : new[] { ( (MemberExpression)selector.Body ).Member.Name };
            
            var sourceType = typeof( TSource );
            var allowedSelectTypes = new Type[] { typeof( string ), typeof( ValueType ) };
            var sourceProperties = sourceType.GetProperties( BindingFlags.Public | BindingFlags.Instance ).Where( p => allowedSelectTypes.Any( t => t.IsAssignableFrom( ( (PropertyInfo)p ).PropertyType ) ) ).Select( p => ( (MemberInfo)p ).Name );
            var sourceFields = sourceType.GetFields( BindingFlags.Public | BindingFlags.Instance ).Where( f => allowedSelectTypes.Any( t => t.IsAssignableFrom( ( (FieldInfo)f ).FieldType ) ) ).Select( f => ( (MemberInfo)f ).Name );

            var selectFields = sourceProperties.Concat( sourceFields ).Where( p => !excludeProperties.Contains( p ) ).ToArray();
            
            var dynamicSelect = 
                    string.Format( "new( {0} )",
                            string.Join( ", ", selectFields ) );

            return selectFields.Count() > 0
                ? source.Select( dynamicSelect )
                : Enumerable.Empty<TSource>().AsQueryable<TSource>();
        }

        private static bool IsCsvExportAllowed( Type memberType, MemberInfo member )
        {
            var allowedCsvTypes = new Type[] { typeof( string ), typeof( ValueType ) };
            if ( !allowedCsvTypes.Any( t => t.IsAssignableFrom( memberType ) ) ) return false;

            var scaffoldColumnAttrib = member.GetCustomAttributes( typeof( ScaffoldColumnAttribute ), true ).Cast<ScaffoldColumnAttribute>().FirstOrDefault(); 
            // BTR Tahiti attribute.
            var xmlPropertyMappingAttr = member.GetCustomAttributes( true ).Cast<Attribute>().Where( a => a.GetType().Name == "XmlPropertyMappingAttribute" ).FirstOrDefault();
            
            return scaffoldColumnAttrib == null || scaffoldColumnAttrib.Scaffold || xmlPropertyMappingAttr != null;
        }

        /// <summary>
        /// Gets the header name.  Didn't directly reference BTR's Tahiti assemblies, but instead did late binding/reflection to determine if there.
        /// </summary>
        /// <param name="member"></param>
        /// <returns></returns>
        private static string CsvHeaderName( MemberInfo member )
        {
            var attributes = member.GetCustomAttributes( true ).Cast<Attribute>();
            
            var exportNameAttr = attributes.Where( a => a.GetType().Name == "ExportNameAttribute" ).FirstOrDefault();
            var xmlPropertyMappingAttr = attributes.Where( a => a.GetType().Name == "XmlPropertyMappingAttribute" ).FirstOrDefault();
            var displayAttr = attributes.Where( a => a.GetType().Name == "DisplayAttribute" ).FirstOrDefault();
            var authIdAttr = attributes.Where( a => a.GetType().Name == "AuthIdAttribute" ).FirstOrDefault();
            
            var name = exportNameAttr != null
                ? (string)exportNameAttr.GetType().GetProperty( "Name" ).GetValue( exportNameAttr, null )
                : xmlPropertyMappingAttr != null
                    ? (string)xmlPropertyMappingAttr.GetType().GetProperty( "XmlField" ).GetValue( xmlPropertyMappingAttr, null )
                    : displayAttr != null
                        ? (string)displayAttr.GetType().GetMethod( "GetName" ).Invoke( displayAttr, null ) 
                        : member.Name;

            return authIdAttr != null ? name + "/key" : name;
        }

        private static void DumpValue( object v, StreamWriter writer )
		{
			if ( v != null )
			{
				switch ( Type.GetTypeCode( v.GetType() ) )
				{
					// csv encode the value
                    case TypeCode.String:
                        string value = (string)v;
						if ( value.Contains( "," ) || value.Contains( '"' ) || value.Contains( "\n" ) )
						{
							value = value.Replace( "\"", "\"\"" );

							if ( value.Length > 31735 )
							{
								value = value.Substring( 0, 31732 ) + "...";
							}
							writer.Write( "\"" + value + "\"" );
						}
						else
						{
							writer.Write( value );
						}
						break;
					
					default: writer.Write( v ); break;
				}
			}
		}

		public static bool IsAnonymous( this Type type )
		{
			if ( type == null )
				throw new ArgumentNullException( "type" );

			// HACK: The only way to detect anonymous types right now.
			return Attribute.IsDefined( type, typeof( CompilerGeneratedAttribute ), false )
					   && type.IsGenericType && type.Name.Contains( "AnonymousType" )
					   && ( type.Name.StartsWith( "<>" ) || type.Name.StartsWith( "VB$" ) )
					   && ( type.Attributes & TypeAttributes.NotPublic ) == TypeAttributes.NotPublic;

		}

		/// <summary>
		/// Batches together multiple IQueryable queries into a single DbCommand and returns all data in
		/// a single roundtrip to the database.
		/// </summary>
		/// <param name="context">The DataContext to execute the batch select against.</param>
		/// <param name="queries">Represents a collections of SELECT queries to execute.</param>
		/// <returns>Returns an IMultipleResults object containing all results.</returns>
		public static IMultipleResults SelectMutlipleResults( this DataContext context, IQueryable[] queries )
		{
			var commandList = new List<DbCommand>();
			
			foreach ( IQueryable query in queries )
			{
				var command = context.GetCommand( query );
				commandList.Add( command );
			}

			SqlCommand batchCommand = CombineCommands( commandList );
			batchCommand.Connection = context.Connection as SqlConnection;

			DbDataReader dr = null;

			if ( batchCommand.Connection.State == ConnectionState.Closed )
			{
				batchCommand.Connection.Open();
				dr = batchCommand.ExecuteReader( CommandBehavior.CloseConnection );
			}
			else
			{
				dr = batchCommand.ExecuteReader();
			}

			IMultipleResults mr = context.Translate( dr );
			return mr;
		}

		/// <summary>
		/// Combines multiple SELECT commands into a single SqlCommand so that all statements can be executed in a 
		/// single roundtrip to the database and return multiple result sets.
		/// </summary>
		/// <param name="commandList">Represents a collection of commands to be batched together.</param>
		/// <returns>Returns a single SqlCommand that executes all SELECT statements at once.</returns>
		private static SqlCommand CombineCommands( List<DbCommand> selectCommands )
		{
			SqlCommand batchCommand = new SqlCommand();
			SqlParameterCollection newParamList = batchCommand.Parameters;
		
			int commandCount = 0;
			
			foreach ( DbCommand cmd in selectCommands )
			{
				string commandText = cmd.CommandText;
				DbParameterCollection paramList = cmd.Parameters;
				int paramCount = paramList.Count;
			
				for ( int currentParam = paramCount - 1; currentParam >= 0; currentParam-- )
				{
					DbParameter param = paramList[ currentParam ];
					DbParameter newParam = CloneParameter( param );
					string newParamName = param.ParameterName.Replace( "@", string.Format( "@{0}_", commandCount ) );
					commandText = commandText.Replace( param.ParameterName, newParamName );
					newParam.ParameterName = newParamName;
					newParamList.Add( newParam );
				}
				if ( batchCommand.CommandText.Length > 0 )
				{
					batchCommand.CommandText += ";";
				}
				batchCommand.CommandText += commandText;
				commandCount++;
			}
			
			return batchCommand;
		}

		/// <summary>
		/// Returns a clone (via copying all properties) of an existing DbParameter.
		/// </summary>
		/// <param name="src">The DbParameter to clone.</param>
		/// <returns>Returns a clone (via copying all properties) of an existing DbParameter.</returns>
		private static DbParameter CloneParameter( DbParameter src )
		{
			SqlParameter source = (SqlParameter)src;
			SqlParameter destination = new SqlParameter();

			destination.Value = source.Value;
			destination.Direction = source.Direction;
			destination.Size = source.Size;
			destination.Offset = source.Offset;
			destination.SourceColumn = source.SourceColumn;
			destination.SourceVersion = source.SourceVersion;
			destination.SourceColumnNullMapping = source.SourceColumnNullMapping;
			destination.IsNullable = source.IsNullable;

			destination.CompareInfo = source.CompareInfo;
			destination.XmlSchemaCollectionDatabase = source.XmlSchemaCollectionDatabase;
			destination.XmlSchemaCollectionOwningSchema = source.XmlSchemaCollectionOwningSchema;
			destination.XmlSchemaCollectionName = source.XmlSchemaCollectionName;
			destination.UdtTypeName = source.UdtTypeName;
			destination.TypeName = source.TypeName;
			destination.ParameterName = source.ParameterName;
			destination.Precision = source.Precision;
			destination.Scale = source.Scale;

			return destination;
		}

		/// <summary>
		/// Immediately deletes all entities from the collection with a single delete command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
		/// <typeparam name="TPrimaryKey">Represents the object type for the primary key of rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="primaryKey">Represents the primary key of the item to be removed from <paramref name="table"/>.</param>
		/// <returns>The number of rows deleted from the database (maximum of 1).</returns>
		/// <remarks>
		/// <para>If the primary key for <paramref name="table"/> is a composite key, <paramref name="primaryKey"/> should be an anonymous type with property names mapping to the property names of objects of type <typeparamref name="TEntity"/>.</para>
		/// </remarks>
		public static int DeleteByPK<TEntity>( this Table<TEntity> table, object primaryKey ) where TEntity : class
		{
			DbCommand delete = table.GetDeleteByPKCommand<TEntity>( primaryKey );

			var parameters = from p in delete.Parameters.Cast<DbParameter>()
							 select p.Value;

			return table.Context.ExecuteCommand( delete.CommandText, parameters.ToArray() );
		}

		/// <summary>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete a entity row via the supplied primary key.</summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
		/// <typeparam name="TPrimaryKey">Represents the object type for the primary key of rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="primaryKey">Represents the primary key of the item to be removed from <paramref name="table"/>.</param>
		/// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete a entity row via the supplied primary key.</returns>
		/// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
		public static string DeleteByPKPreview<TEntity>( this Table<TEntity> table, object primaryKey ) where TEntity : class
		{
			DbCommand delete = table.GetDeleteByPKCommand<TEntity>( primaryKey );
			return delete.PreviewCommandText( false ) + table.Context.GetLog();
		}

		private static DbCommand GetDeleteByPKCommand<TEntity>( this Table<TEntity> table, object primaryKey ) where TEntity : class
		{
			Type type = primaryKey.GetType();
			bool typeIsAnonymous = type.IsAnonymous();
			string dbName = table.GetDbName();

			var metaTable = table.Context.Mapping.GetTable( typeof( TEntity ) );

			var keys = from mdm in metaTable.RowType.DataMembers
					   where mdm.IsPrimaryKey
					   select new { mdm.MappedName, mdm.Name, mdm.Type };

			SqlCommand deleteCommand = new SqlCommand();
			deleteCommand.Connection = table.Context.Connection as SqlConnection;

			var whereSB = new StringBuilder();

			foreach ( var key in keys )
			{
				// Add new parameter with massaged name to avoid clashes.
				whereSB.AppendFormat( "[{0}] = @p{1}, ", key.MappedName, deleteCommand.Parameters.Count );

				object value = primaryKey;
				if ( typeIsAnonymous || ( type.IsClass && type != typeof( string ) ) )
				{
					if ( typeIsAnonymous )
					{
						PropertyInfo property = type.GetProperty( key.Name );

						if ( property == null )
						{
							throw new ArgumentOutOfRangeException( string.Format( "The property {0} which is defined as part of the primary key for {1} was not supplied by the parameter primaryKey.", key.Name, metaTable.TableName ) );
						}

						value = property.GetValue( primaryKey, null );
					}
					else
					{
						FieldInfo field = type.GetField( key.Name );

						if ( field == null )
						{
							throw new ArgumentOutOfRangeException( string.Format( "The property {0} which is defined as part of the primary key for {1} was not supplied by the parameter primaryKey.", key.Name, metaTable.TableName ) );
						}

						value = field.GetValue( primaryKey );
					}

					if ( value.GetType() != key.Type )
					{
						throw new InvalidCastException( string.Format( "The property {0} ({1}) does not have the same type as {2} ({3}).", key.Name, value.GetType(), key.MappedName, key.Type ) );
					}
				}
				else if ( value.GetType() != key.Type )
				{
					throw new InvalidCastException( string.Format( "The value supplied in primaryKey ({0}) does not have the same type as {1} ({2}).", value.GetType(), key.MappedName, key.Type ) );
				}

				deleteCommand.Parameters.Add( new SqlParameter( string.Format( "@p{0}", deleteCommand.Parameters.Count ), value ) );
			}

			string wherePK = whereSB.ToString();

			if ( wherePK == "" )
			{
				throw new MissingPrimaryKeyException( string.Format( "{0} does not have a primary key defined.  Batch updating/deleting can not be used for tables without a primary key.", metaTable.TableName ) );
			}

			deleteCommand.CommandText = string.Format( "DELETE {0}\r\nWHERE {1}", dbName, wherePK.Substring( 0, wherePK.Length - 2 ) );

			return deleteCommand;
		}

		/// <summary>
		/// Immediately deletes all entities from the collection with a single delete command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
		/// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
		/// <returns>The number of rows deleted from the database.</returns>
		/// <remarks>
		/// <para>Similiar to stored procedures, and opposite from DeleteAllOnSubmit, rows provided in <paramref name="entities"/> will be deleted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
		/// <para>Additionally, to improve performance, instead of creating a delete command for each item in <paramref name="entities"/>, a single delete command is created.</para>
		/// </remarks>
		public static int DeleteBatch<TEntity>( this Table<TEntity> table, IQueryable<TEntity> entities ) where TEntity : class
		{
			DbCommand delete = table.GetDeleteBatchCommand<TEntity>( entities );

			var parameters = from p in delete.Parameters.Cast<DbParameter>()
							 select p.Value;

			return table.Context.ExecuteCommand( delete.CommandText, parameters.ToArray() );
		}

		/// <summary>
		/// Immediately deletes all entities from the collection with a single delete command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
		/// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
		/// <returns>The number of rows deleted from the database.</returns>
		/// <remarks>
		/// <para>Similiar to stored procedures, and opposite from DeleteAllOnSubmit, rows provided in <paramref name="entities"/> will be deleted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
		/// <para>Additionally, to improve performance, instead of creating a delete command for each item in <paramref name="entities"/>, a single delete command is created.</para>
		/// </remarks>
		public static int DeleteBatch<TEntity>( this Table<TEntity> table, Expression<Func<TEntity, bool>> filter ) where TEntity : class
		{
			return table.DeleteBatch( table.Where( filter ) );
		}

		/// <summary>
		/// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
		/// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
		/// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
		/// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
		public static string DeleteBatchPreview<TEntity>( this Table<TEntity> table, IQueryable<TEntity> entities ) where TEntity : class
		{
			DbCommand delete = table.GetDeleteBatchCommand<TEntity>( entities );
			return "Total Rows To Be Deleted By Query: " + entities.Count() + "\n\n" + delete.PreviewCommandText( false ) + table.Context.GetLog();
		}

		/// <summary>
		/// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
		/// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
		/// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
		/// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
		public static string DeleteBatchPreview<TEntity>( this Table<TEntity> table, Expression<Func<TEntity, bool>> filter ) where TEntity : class
		{
			return table.DeleteBatchPreview( table.Where( filter ) );
		}

		/// <summary>
		/// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
		/// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
		/// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
		/// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks>
		public static string DeleteBatchSQL<TEntity>( this Table<TEntity> table, IQueryable<TEntity> entities ) where TEntity : class
		{
			DbCommand delete = table.GetDeleteBatchCommand<TEntity>( entities );
			return delete.PreviewCommandText( true );
		}

		/// <summary>
		/// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
		/// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
		/// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
		/// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks>
		public static string DeleteBatchSQL<TEntity>( this Table<TEntity> table, Expression<Func<TEntity, bool>> filter ) where TEntity : class
		{
			return table.DeleteBatchSQL( table.Where( filter ) );
		}

		/// <summary>
		/// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
		/// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param>
		/// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
		/// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns>
		/// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
		public static string UpdateBatchPreview<TEntity>( this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator ) where TEntity : class
		{
			DbCommand update = table.GetUpdateBatchCommand<TEntity>( entities, evaluator );
            return "Total Rows To Be Updated By Query: " + entities.Count() + "\n\n" + update.PreviewCommandText( false ) + table.Context.GetLog();
		}

		/// <summary>
		/// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
		/// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
		/// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
		/// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns>
		/// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
		public static string UpdateBatchPreview<TEntity>( this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator ) where TEntity : class
		{
			return table.UpdateBatchPreview( table.Where( filter ), evaluator );
		}

		/// <summary>
		/// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
		/// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param>
		/// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
		/// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns>
		/// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks>
		public static string UpdateBatchSQL<TEntity>( this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator ) where TEntity : class
		{
			DbCommand update = table.GetUpdateBatchCommand<TEntity>( entities, evaluator );
			return update.PreviewCommandText( true );
		}

		/// <summary>
		/// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
		/// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
		/// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
		/// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns>
		/// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks>
		public static string UpdateBatchSQL<TEntity>( this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator ) where TEntity : class
		{
			return table.UpdateBatchSQL( table.Where( filter ), evaluator );
		}

		/// <summary>
		/// Immediately updates all entities in the collection with a single update command based on a <typeparamref name="TEntity"/> created from a Lambda expression.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
		/// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param>
		/// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
		/// <returns>The number of rows updated in the database.</returns>
		/// <remarks>
		/// <para>Similiar to stored procedures, and opposite from similiar InsertAllOnSubmit, rows provided in <paramref name="entities"/> will be updated immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
		/// <para>Additionally, to improve performance, instead of creating an update command for each item in <paramref name="entities"/>, a single update command is created.</para>
		/// </remarks>
		public static int UpdateBatch<TEntity>( this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator ) where TEntity : class
		{
			DbCommand update = table.GetUpdateBatchCommand<TEntity>( entities, evaluator );

			var parameters = from p in update.Parameters.Cast<DbParameter>()
							 select p.Value;
			return table.Context.ExecuteCommand( update.CommandText, parameters.ToArray() );
		}

		/// <summary>
		/// Immediately updates all entities in the collection with a single update command based on a <typeparamref name="TEntity"/> created from a Lambda expression.
		/// </summary>
		/// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
		/// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
		/// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
		/// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
		/// <returns>The number of rows updated in the database.</returns>
		/// <remarks>
		/// <para>Similiar to stored procedures, and opposite from similiar InsertAllOnSubmit, rows provided in <paramref name="entities"/> will be updated immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
		/// <para>Additionally, to improve performance, instead of creating an update command for each item in <paramref name="entities"/>, a single update command is created.</para>
		/// </remarks>
		public static int UpdateBatch<TEntity>( this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator ) where TEntity : class
		{
			return table.UpdateBatch( table.Where( filter ), evaluator );
		}

		/// <summary>
		/// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to perform the query's select statement.
		/// </summary>
		/// <param name="context">The DataContext to execute the batch select against.</param>
		/// <param name="query">Represents the SELECT query to execute.</param>
		/// <returns>Returns the Transact SQL string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns>
		/// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
		public static string PreviewSQL( this DataContext context, IQueryable query )
		{
			var cmd = context.GetCommand( query );
			return cmd.PreviewCommandText( true );
		}

		/// <summary>
		/// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to perform the query's select statement.
		/// </summary>
		/// <param name="context">The DataContext to execute the batch select against.</param>
		/// <param name="query">Represents the SELECT query to execute.</param>
		/// <returns>Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns>
		/// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
		public static string PreviewCommandText( this DataContext context, IQueryable query )
		{
			var cmd = context.GetCommand( query );
			return cmd.PreviewCommandText( false);
		}

		/// <summary>
		/// Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.
		/// </summary>
		/// <param name="cmd">The <see cref="DbCommand"/> to analyze.</param>
		/// <param name="forTransactSQL">Whether or not the text should be formatted as 'logging' similiar to LINQ to SQL output, or in valid Transact SQL syntax ready for use with a 'query analyzer' type tool.</param>
		/// <returns>Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns>
		/// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
		private static string PreviewCommandText( this DbCommand cmd, bool forTransactSQL )
		{
			var output = new StringBuilder();
			
			if ( !forTransactSQL ) output.AppendLine( cmd.CommandText );

			foreach ( DbParameter parameter in cmd.Parameters )
			{
				int num = 0;
				int num2 = 0;
				PropertyInfo property = parameter.GetType().GetProperty( "Precision" );
				if ( property != null )
				{
					num = (int)Convert.ChangeType( property.GetValue( parameter, null ), typeof( int ), CultureInfo.InvariantCulture );
				}
				PropertyInfo info2 = parameter.GetType().GetProperty( "Scale" );
				if ( info2 != null )
				{
					num2 = (int)Convert.ChangeType( info2.GetValue( parameter, null ), typeof( int ), CultureInfo.InvariantCulture );
				}
				SqlParameter parameter2 = parameter as SqlParameter;

				if ( forTransactSQL )
				{
					output.AppendFormat( "DECLARE {0} {1}{2}; SET {0} = {3}\r\n", 
						new object[] { 
								parameter.ParameterName, 
								( parameter2 == null ) ? parameter.DbType.ToString() : parameter2.SqlDbType.ToString(), 
								( parameter.Size > 0 ) ? "( " + parameter.Size.ToString( CultureInfo.CurrentCulture ) + " )" : "", 
								GetParameterTransactValue( parameter, parameter2 ) } );
				}
				else
				{
					output.AppendFormat( "-- {0}: {1} {2} (Size = {3}; Prec = {4}; Scale = {5}) [{6}]\r\n", new object[] { parameter.ParameterName, parameter.Direction, ( parameter2 == null ) ? parameter.DbType.ToString() : parameter2.SqlDbType.ToString(), parameter.Size.ToString( CultureInfo.CurrentCulture ), num, num2, ( parameter2 == null ) ? parameter.Value : parameter2.SqlValue } );
				}
			}

			if ( forTransactSQL ) output.Append( "\r\n" + cmd.CommandText );

			return output.ToString();
		}

		private static string GetParameterTransactValue( DbParameter parameter, SqlParameter parameter2 )
		{
			if ( parameter2 == null ) return parameter.Value.ToString(); // Not going to deal with NON SQL parameters.

			switch( parameter2.SqlDbType ) 
			{
				case SqlDbType.Char:
				case SqlDbType.Date:
				case SqlDbType.DateTime:
				case SqlDbType.DateTime2:
				case SqlDbType.NChar:
				case SqlDbType.NText:
				case SqlDbType.NVarChar:
				case SqlDbType.SmallDateTime:
				case SqlDbType.Text:
				case SqlDbType.VarChar:
				case SqlDbType.UniqueIdentifier:
					return string.Format( "'{0}'", parameter2.SqlValue );

				default:
					return parameter2.SqlValue.ToString();
			}
		}

		private static DbCommand GetDeleteBatchCommand<TEntity>( this Table<TEntity> table, IQueryable<TEntity> entities ) where TEntity : class
		{
			var deleteCommand = table.Context.GetCommand( entities );
			deleteCommand.CommandText = string.Format( "DELETE {0}\r\n", table.GetDbName() ) + GetBatchJoinQuery<TEntity>( table, entities );
			return deleteCommand;
		}

		private static DbCommand GetUpdateBatchCommand<TEntity>( this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator ) where TEntity : class
		{
			var updateCommand = table.Context.GetCommand( entities );

			var setSB = new StringBuilder();
			int memberInitCount = 1;

			// Process the MemberInitExpression (there should only be one in the evaluator Lambda) to convert the expression tree
			// into a valid DbCommand.  The Visit<> method will only process expressions of type MemberInitExpression and requires
			// that a MemberInitExpression be returned - in our case we'll return the same one we are passed since we are building
			// a DbCommand and not 'really using' the evaluator Lambda.
			evaluator.Visit<MemberInitExpression>( delegate( MemberInitExpression expression )
			{
				if ( memberInitCount > 1 )
				{
					throw new NotImplementedException( "Currently only one MemberInitExpression is allowed for the evaluator parameter." );
				}
				memberInitCount++;

				setSB.Append( GetDbSetStatement<TEntity>( expression, table, updateCommand ) );
				
				return expression; // just return passed in expression to keep 'visitor' happy.
			} );

			// Complete the command text by concatenating bits together.
			updateCommand.CommandText = string.Format( "UPDATE {0}\r\n{1}\r\n\r\n{2}", 
															table.GetDbName(),									// Database table name
															setSB.ToString(),									// SET fld = {}, fld2 = {}, ...
															GetBatchJoinQuery<TEntity>( table, entities ) );	// Subquery join created from entities command text

			if ( updateCommand.CommandText.IndexOf( "[arg0]" ) >= 0 || updateCommand.CommandText.IndexOf( "NULL AS [EMPTY]" ) >= 0 )
			{
				// TODO (Chris): Probably a better way to determine this by using an visitor on the expression before the
				//				 var selectExpression = Expression.Call... method call (search for that) and see which funcitons
				//				 are being used and determine if supported by LINQ to SQL
				throw new NotSupportedException( string.Format( "The evaluator Expression<Func<{0},{0}>> has processing that needs to be performed once the query is returned (i.e. string.Format()) and therefore can not be used during batch updating.", table.GetType() ) );
			}

			return updateCommand;
		}

		private static string GetDbSetStatement<TEntity>( MemberInitExpression memberInitExpression, Table<TEntity> table, DbCommand updateCommand ) where TEntity : class
		{
			var entityType = typeof( TEntity );

			if ( memberInitExpression.Type != entityType )
			{
				throw new NotImplementedException( string.Format( "The MemberInitExpression is intializing a class of the incorrect type '{0}' and it should be '{1}'.", memberInitExpression.Type, entityType ) );
			}

			var setSB = new StringBuilder();

			var tableName = table.GetDbName();
			var metaTable = table.Context.Mapping.GetTable( entityType );
			// Used to look up actual field names when MemberAssignment is a constant,
			// need both the Name (matches the property name on LINQ object) and the
			// MappedName (db field name).
			var dbCols = from mdm in metaTable.RowType.DataMembers
						 select new { mdm.MappedName, mdm.Name };

			// Walk all the expression bindings and generate SQL 'commands' from them.  Each binding represents a property assignment
			// on the TEntity object initializer Lambda expression.
			foreach ( var binding in memberInitExpression.Bindings )
			{
				var assignment = binding as MemberAssignment;

				if ( binding == null )
				{
					throw new NotImplementedException( "All bindings inside the MemberInitExpression are expected to be of type MemberAssignment." );
				}

				// TODO (Document): What is this doing?  I know it's grabbing existing parameter to pass into Expression.Call() but explain 'why'
				//					I assume it has something to do with fact we can't just access the parameters of assignment.Expression?
				//					Also, any concerns of whether or not if there are two params of type entity type?
				ParameterExpression entityParam = null;
				assignment.Expression.Visit<ParameterExpression>( delegate( ParameterExpression p ) { if ( p.Type == entityType ) entityParam = p; return p; } );

				// Get the real database field name.  binding.Member.Name is the 'property' name of the LINQ object
				// so I match that to the Name property of the table mapping DataMembers.
				string name = binding.Member.Name;
				var dbCol = ( from c in dbCols
							  where c.Name == name
							  select c ).FirstOrDefault();

				if ( dbCol == null )
				{
					throw new ArgumentOutOfRangeException( name, string.Format( "The corresponding field on the {0} table could not be found.", tableName ) );
				}

				var mappedName = dbCol.MappedName.StartsWith( "[" ) ? dbCol.MappedName : string.Format( "[{0}]", dbCol.MappedName );

				// If entityParam is NULL, then no references to other columns on the TEntity row and need to eval 'constant' value...
				if ( entityParam == null )
				{
					// Compile and invoke the assignment expression to obtain the contant value to add as a parameter.
					var constant = Expression.Lambda( assignment.Expression, null ).Compile().DynamicInvoke();

					// use the MappedName from the table mapping DataMembers - that is field name in DB table.
					if ( constant == null )
					{
						setSB.AppendFormat( "{0} = null, ", mappedName );
					}
					else
					{
						// Add new parameter with massaged name to avoid clashes.
						setSB.AppendFormat( "{0} = @p{1}, ", mappedName, updateCommand.Parameters.Count );
						updateCommand.Parameters.Add( new SqlParameter( string.Format( "@p{0}", updateCommand.Parameters.Count ), constant ) );
					}
				}
				else
				{
					// TODO (Documentation): Explain what we are doing here again, I remember you telling me why we have to call but I can't remember now.
					// Wny are we calling Expression.Call and what are we passing it?  Below comments are just 'made up' and probably wrong.

					// Create a MethodCallExpression which represents a 'simple' select of *only* the assignment part (right hand operator) of
					// of the MemberInitExpression.MemberAssignment so that we can let the Linq Provider do all the 'sql syntax' generation for
					// us. 
					//
					// For Example: TEntity.Property1 = TEntity.Property1 + " Hello"
					// This selectExpression will be only dealing with TEntity.Property1 + " Hello"
					var selectExpression = Expression.Call(
												typeof( Queryable ),
												"Select",
												new Type[] { entityType, assignment.Expression.Type },

					// TODO (Documentation): How do we know there are only 'two' parameters?  And what is Expression.Lambda
						//						 doing?  I assume it's returning a type of assignment.Expression.Type to match above?

												Expression.Constant( table ),
												Expression.Lambda( assignment.Expression, entityParam ) );

					setSB.AppendFormat( "{0} = {1}, ",
											mappedName,
											GetDbSetAssignment( table, selectExpression, updateCommand, name ) );
				}
			}

			var setStatements = setSB.ToString();
			return "SET " + setStatements.Substring( 0, setStatements.Length - 2 ); // remove ', '
		}

		/// <summary>
		/// Some LINQ Query syntax is invalid because SQL (or whomever the provider is) can not translate it to its native language.  
		/// DataContext.GetCommand() does not detect this, only IProvider.Execute or IProvider.Compile call the necessary code to 
		/// check this.  This function invokes the IProvider.Compile to make sure the provider can translate the expression.
		/// </summary>
		/// <remarks>
		/// An example of a LINQ query that previously 'worked' in the *Batch methods but needs to throw an exception is something
		/// like the following:
		/// 
		/// var pay = 
		///		from h in HistoryData
		///		where h.his.Groups.gName == "Ochsner" && h.hisType == "pay"
		///		select h;
		///		
		/// HistoryData.UpdateBatchPreview( pay, h => new HistoryData { hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString() } ).Dump();
		/// 
		/// The int.Parse is not valid and needs to throw an exception like: 
		/// 
		///		Could not translate expression '(Parse(p.hisIndex) - 1).ToString()' into SQL and could not treat it as a local expression.
		///		
		///	Unfortunately, the IProvider.Compile is internal and I need to use Reflection to call it (ugh).  I've several e-mails sent into
		///	MS LINQ team members and am waiting for a response and will correct/improve code as soon as possible.
		/// </remarks>
		private static void ValidateExpression( ITable table, Expression expression )
		{
			// Simply compile the expression to see if it will work.
			// Needed to change this to use expression tree.  Original code (below) could fail with DataContextInterceptor.

			var compile = Expression.Call(
				Expression.Property( Expression.Constant( table.Context ), "Provider" ),
				"Compile",
				null,
				Expression.Constant( expression ) );

			Expression.Lambda<Action>( compile ).Compile()();
/*
			var context = table.Context;
			PropertyInfo providerProperty = context.GetType().GetProperty( "Provider", BindingFlags.Instance | BindingFlags.NonPublic );
			var provider = providerProperty.GetValue( context, null );
			var compileMI = provider.GetType().GetMethod( "System.Data.Linq.Provider.IProvider.Compile", BindingFlags.Instance | BindingFlags.NonPublic );

			compileMI.Invoke( provider, new object[] { expression } );*/
		}

		private static string GetDbSetAssignment( ITable table, MethodCallExpression selectExpression, DbCommand updateCommand, string bindingName )
		{
			ValidateExpression( table, selectExpression );

			// Convert the selectExpression into an IQueryable query so that I can get the CommandText
			var selectQuery = ( table as IQueryable ).Provider.CreateQuery( selectExpression );

			// Get the DbCommand so I can grab relavent parts of CommandText to construct a field 
			// assignment and based on the 'current TEntity row'.  Additionally need to massage parameter 
			// names from temporary command when adding to the final update command.
			var selectCmd = table.Context.GetCommand( selectQuery );
			var selectStmt = selectCmd.CommandText;
			selectStmt = selectStmt.Substring( 7,									// Remove 'SELECT ' from front ( 7 )
										selectStmt.IndexOf( "\r\nFROM " ) - 7 )		// Return only the selection field expression
									.Replace( "[t0].", "" )							// Remove table alias from the select
									.Replace( " AS [value]", "" )					// If the select is not a direct field (constant or expression), remove the field alias
									.Replace( "@p", "@p" + bindingName );			// Replace parameter name so doesn't conflict with existing ones.

			foreach ( var selectParam in selectCmd.Parameters.Cast<DbParameter>() )
			{
				var paramName = string.Format( "@p{0}", updateCommand.Parameters.Count );

				// DataContext.ExecuteCommand ultimately just takes a object array of parameters and names them p0-N.  
				// So I need to now do replaces on the massaged value to get it in proper format.
				selectStmt = selectStmt.Replace( 
									selectParam.ParameterName.Replace( "@p", "@p" + bindingName ), 
									paramName );

				updateCommand.Parameters.Add( new SqlParameter( paramName, selectParam.Value ) );
			}

			return selectStmt;
		}

		private static string GetBatchJoinQuery<TEntity>( Table<TEntity> table, IQueryable<TEntity> entities ) where TEntity : class
		{
			var metaTable = table.Context.Mapping.GetTable( typeof( TEntity ) );

			var keys = from mdm in metaTable.RowType.DataMembers
					   where mdm.IsPrimaryKey
					   select new { mdm.MappedName };

			var joinSB = new StringBuilder();
			var subSelectSB = new StringBuilder();
			
			foreach ( var key in keys )
			{
				joinSB.AppendFormat( "j0.[{0}] = j1.[{0}] AND ", key.MappedName );
				// For now, always assuming table is aliased as t0.  Should probably improve at some point.
				// Just writing a smaller sub-select so it doesn't get all the columns of data, but instead
				// only the primary key fields used for joining.
				subSelectSB.AppendFormat( "[t0].[{0}], ", key.MappedName );
			}

			var selectCommand = table.Context.GetCommand( entities );
			var select = selectCommand.CommandText;

			var join = joinSB.ToString();

			if ( join == "" )
			{
				throw new MissingPrimaryKeyException( string.Format( "{0} does not have a primary key defined.  Batch updating/deleting can not be used for tables without a primary key.", metaTable.TableName ) );
			}

			join = join.Substring( 0, join.Length - 5 );											// Remove last ' AND '
			#region - Better ExpressionTree Handling Needed -
			/*
			
			Below is a sample query where the let statement was used to simply the 'where clause'.  However, it produced an extra level
			in the query.
			 
			var manage =
				from u in User
				join g in Groups on u.User_Group_id equals g.gKey into groups
				from g in groups.DefaultIfEmpty()
				let correctGroup = groupsToManage.Contains( g.gName ) || ( groupsToManage.Contains( "_GLOBAL" ) && g.gKey == null )
				where correctGroup && ( users.Contains( u.User_Authenticate_id ) || userEmails.Contains( u.User_Email ) ) || userKeys.Contains( u.User_id )
				select u;
			 
			Produces this SQL:
			SELECT [t2].[User_id] AS [uKey], [t2].[User_Authenticate_id] AS [uAuthID], [t2].[User_Email] AS [uEmail], [t2].[User_Pin] AS [uPin], [t2].[User_Active] AS [uActive], [t2].[uAdminAuthID], [t2].[uFailureCount]
			FROM (
				SELECT [t0].[User_id], [t0].[User_Authenticate_id], [t0].[User_Email], [t0].[User_Pin], [t0].[User_Active], [t0].[uFailureCount], [t0].[uAdminAuthID], 
					(CASE 
						WHEN [t1].[gName] IN (@p0) THEN 1
						WHEN NOT ([t1].[gName] IN (@p0)) THEN 0
						ELSE NULL
					 END) AS [value]
				FROM [User] AS [t0]
				LEFT OUTER JOIN [Groups] AS [t1] ON [t0].[User_Group_id] = ([t1].[gKey])
				) AS [t2]
			WHERE (([t2].[value] = 1) AND (([t2].[User_Authenticate_id] IN (@p1)) OR ([t2].[User_Email] IN (@p2)))) OR ([t2].[User_id] IN (@p3))			 
			
			If I put the entire where in one line...
			where 	( groupsToManage.Contains( g.gName ) || ( groupsToManage.Contains( "_GLOBAL" ) && g.gKey == null ) ) && 
					( users.Contains( u.User_Authenticate_id ) || userEmails.Contains( u.User_Email ) ) || userKeys.Contains ( u.User_id )

			I get this SQL:
			SELECT [t0].[User_id] AS [uKey], [t0].[User_Authenticate_id] AS [uAuthID], [t0].[User_Email] AS [uEmail], [t0].[User_Pin] AS [uPin], [t0].[User_Active] AS [uActive], [t0].[uAdminAuthID], [t0].[uFailureCount]
			FROM [User] AS [t0]
			LEFT OUTER JOIN [Groups] AS [t1] ON [t0].[User_Group_id] = ([t1].[gKey])
			WHERE (([t1].[gName] IN (@p0)) AND (([t0].[User_Authenticate_id] IN (@p1)) OR ([t0].[User_Email] IN (@p2)))) OR ([t0].[User_id] IN (@p3))			
			
			The second 'cleaner' SQL worked with my original 'string parsing' of simply looking for [t0] and stripping everything before it
			to get rid of the SELECT and any 'TOP' clause if present.  But the first SQL introduced a layer which caused [t2] to be used.  So
			I have to do a bit different string parsing.  There is probably a more efficient way to examine the ExpressionTree and figure out
			if something like this is going to happen.  I will explore it later.
			*/
			#endregion
			var endSelect = select.IndexOf( "[t" );													// Get 'SELECT ' and any TOP clause if present
			var selectClause = select.Substring( 0, endSelect );
			var selectTableNameStart = endSelect + 1;												// Get the table name LINQ to SQL used in query generation
			var selectTableName = select.Substring( selectTableNameStart,							// because I have to replace [t0] with it in the subSelectSB
										select.IndexOf( "]", selectTableNameStart ) - ( selectTableNameStart ) );
			
			// TODO (Chris): I think instead of searching for ORDER BY in the entire select statement, I should examine the ExpressionTree and see
			// if the *outer* select (in case there are nested subselects) has an orderby clause applied to it.
			var needsTopClause = selectClause.IndexOf( " TOP " ) < 0 && select.IndexOf( "\r\nORDER BY " ) > 0;
			
			var subSelect = selectClause
								+ ( needsTopClause ? "TOP 100 PERCENT " : "" )							// If order by in original select without TOP clause, need TOP
								+ subSelectSB.ToString()												// Append just the primary keys.
											 .Replace( "[t0]", string.Format( "[{0}]", selectTableName ) );												
			subSelect = subSelect.Substring( 0, subSelect.Length - 2 );									// Remove last ', '

			subSelect += select.Substring( select.IndexOf( "\r\nFROM " ) ); // Create a sub SELECT that *only* includes the primary key fields

			var batchJoin = String.Format( "FROM {0} AS j0 INNER JOIN (\r\n\r\n{1}\r\n\r\n) AS j1 ON ({2})\r\n", table.GetDbName(), subSelect, join );
			return batchJoin;
		}

		private static string GetDbName<TEntity>( this Table<TEntity> table ) where TEntity : class
		{
			var entityType = typeof( TEntity );
			var metaTable = table.Context.Mapping.GetTable( entityType );
			var tableName = metaTable.TableName;

			string[] parts = tableName.Split( '.' );
			tableName = string.Join( ".", parts.Select( p => p.StartsWith( "[" ) ? p : string.Format( "[{0}]", p ) ) );

			return tableName;
		}

		private static string GetLog( this DataContext context )
		{
			PropertyInfo providerProperty = context.GetType().GetProperty( "Provider", BindingFlags.Instance | BindingFlags.NonPublic );
			var provider = providerProperty.GetValue( context, null );
			Type providerType = provider.GetType();

			PropertyInfo modeProperty = providerType.GetProperty( "Mode", BindingFlags.Instance | BindingFlags.NonPublic );
			FieldInfo servicesField = providerType.GetField( "services", BindingFlags.Instance | BindingFlags.NonPublic );
			object services = servicesField != null ? servicesField.GetValue( provider ) : null;
			PropertyInfo modelProperty = services != null ? services.GetType().GetProperty( "Model", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.GetProperty ) : null;

			return string.Format( "-- Context: {0}({1}) Model: {2} Build: {3}\r\n",
							providerType.Name,
							modeProperty != null ? modeProperty.GetValue( provider, null ) : "unknown",
							modelProperty != null ? modelProperty.GetValue( services, null ).GetType().Name : "unknown",
							"3.5.21022.8" );
		}

		/// <summary>
		/// Returns a list of changed items inside the Context before being applied to the data store.
		/// </summary>
		/// <param name="context">The DataContext to interogate for pending changes.</param>
		/// <returns>Returns a list of changed items inside the Context before being applied to the data store.</returns>
		/// <remarks>Based on Ryan Haney's code at http://dotnetslackers.com/articles/csharp/GettingChangedEntitiesFromLINQToSQLDataContext.aspx.  Note that this code relies on reflection of private fields and members.</remarks>
		public static List<ChangedItems<TEntity>> GetChangedItems<TEntity>( this DataContext context )
		{
			// create a dictionary of type TItem for return to caller
			List<ChangedItems<TEntity>> changedItems = new List<ChangedItems<TEntity>>();

			PropertyInfo providerProperty = context.GetType().GetProperty( "Provider", BindingFlags.Instance | BindingFlags.NonPublic );
			var provider = providerProperty.GetValue( context, null );
			Type providerType = provider.GetType();

			// use reflection to get changed items from data context
			object services = providerType.GetField( "services",
			  BindingFlags.NonPublic |
			  BindingFlags.Instance |
			  BindingFlags.GetField ).GetValue( provider );

			object tracker = services.GetType().GetField( "tracker",
			  BindingFlags.NonPublic |
			  BindingFlags.Instance |
			  BindingFlags.GetField ).GetValue( services );

			System.Collections.IDictionary trackerItems =
			  (System.Collections.IDictionary)tracker.GetType().GetField( "items",
			  BindingFlags.NonPublic |
			  BindingFlags.Instance |
			  BindingFlags.GetField ).GetValue( tracker );

			// iterate through each item in context, adding
			// only those that are of type TItem to the changedItems dictionary
			foreach ( System.Collections.DictionaryEntry entry in trackerItems )
			{
				object original = entry.Value.GetType().GetField( "original",
								  BindingFlags.NonPublic |
								  BindingFlags.Instance |
								  BindingFlags.GetField ).GetValue( entry.Value );

				if ( entry.Key is TEntity && original is TEntity )
				{
					changedItems.Add(
					  new ChangedItems<TEntity>( (TEntity)entry.Key, (TEntity)original )
					);
				}
			}
			return changedItems;
		}
	}

	public class ChangedItems<TEntity>
	{
		public ChangedItems( TEntity Current, TEntity Original )
		{
			this.Current = Current;
			this.Original = Original;
		}
		public TEntity Current { get; set; }
		public TEntity Original { get; set; }
	}
}
